<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class m_laporan_stok extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'SELECT *, (SELECT sum(Jumlah) FROM detail_beli WHERE KdObat = obat.KdObat) as beli,
		(SELECT sum(Jumlah) FROM detail_jual WHERE KdObat = obat.KdObat) as jual,
		(SELECT sum(Jumlah) FROM obat_hilang WHERE KdObat = obat.KdObat) as rusak FROM obat 
		INNER JOIN kelompok ON kelompok.KdKelompok = obat.KdKelompok
		ORDER BY NmObat';
		return $sql;
	}

	function get_cari($kelompok='') {
		$sql = 'SELECT *, (SELECT sum(Jumlah) FROM detail_beli WHERE KdObat = obat.KdObat) as beli,
		(SELECT sum(Jumlah) FROM detail_jual WHERE KdObat = obat.KdObat) as jual,
		(SELECT sum(Jumlah) FROM obat_hilang WHERE KdObat = obat.KdObat) as rusak FROM obat 
		INNER JOIN kelompok ON kelompok.KdKelompok = obat.KdKelompok
		WHERE kelompok.KdKelompok = "'.$kelompok.'"
		ORDER BY NmObat';
		return $sql;
	}

	function get_export_all() {
		$sql = 'SELECT *, (SELECT sum(Jumlah) FROM detail_beli WHERE KdObat = obat.KdObat) as beli,
		(SELECT sum(Jumlah) FROM detail_jual WHERE KdObat = obat.KdObat) as jual,
		(SELECT sum(Jumlah) FROM obat_hilang WHERE KdObat = obat.KdObat) as rusak FROM obat
		INNER JOIN kelompok ON kelompok.KdKelompok = obat.Kdkelompok';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_by($KdKelompok) {
		$sql = 'SELECT *, (SELECT sum(Jumlah) FROM detail_beli WHERE KdObat = obat.KdObat) as beli,
		(SELECT sum(Jumlah) FROM detail_jual WHERE KdObat = obat.KdObat) as jual,
		(SELECT sum(Jumlah) FROM obat_hilang WHERE KdObat = obat.KdObat) as rusak FROM obat
		INNER JOIN kelompok ON kelompok.Kdkelompok = obat.KdKelompok 
		WHERE obat.KdKelompok = ?';
		$query = $this->db->query($sql,$KdKelompok);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kelompok() {
		$sql = 'SELECT * FROM kelompok';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kelompok_by($KdKelompok) {
		$sql = 'SELECT * FROM kelompok WHERE KdKelompok = "'.$KdKelompok.'"';
		$query = $this->db->query($sql,$KdKelompok);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['NmKelompok'];
	}
}
